import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {
    public static List<Student> query() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (Exception e){
            System.out.println(e.getMessage());
            throw new RuntimeException();
        }
        //链接数据库
        String url = "jdbc:mysql://localhost:3306/studentmanagement?" +
                "useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
        String user = "root";
        String password = "123456";
        List<Student> students = new ArrayList<>();
        try{
            //链接数据库
            Connection con = DriverManager.getConnection(url,
                    user, password);
            //准备sql语句
            String sql = "select * from student";
            //准备sql执行对象
            Statement stm = con.createStatement();
            //执行sql 得到一个结果集
            ResultSet rs = stm.executeQuery(sql);
            //处理结果集
            while(rs.next()){
                int id = rs.getInt("id");
                String sno = rs.getString("sno");
                String sname = rs.getString("sname");
                String sgrade = rs.getString("sgrade");
                students.add(new Student(sno,sname,sgrade));
            }
            //资源关闭
            rs.close();
            stm.close();
            con.close();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
        return students;
    }
    public static List<Student> queryLike(String sno,String sname,String sgrade){
        List<Student> stus = new ArrayList<>();
        //准备一个sql语句
        String sql = "select * from student where 1=1";
        String url = "jdbc:mysql://localhost:3306/studentmanagement?" +
                "useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "123456";
        if(!"".equals(sno)){
            sql+=" and sno like ?";
        }
        if(!"".equals(sname)){
            sql+=" and sname like ?";
        }
        if(!"".equals(sgrade)){
            sql+=" and sgrade like ?";
        }
        System.out.println(sql);
        try(Connection connection = DriverManager.getConnection(url,user,password)){
            //获得执行的statement
            PreparedStatement pstm = connection.prepareStatement(sql);
            int index = 1;
            if(sql.contains("sno")){
                pstm.setString(index++,"%"+sno+"%");
            }
            if(sql.contains("sname")){
                pstm.setString(index++,"%"+sname+"%");
            }
            if(sql.contains("sgrade")){
                pstm.setString(index,"%"+sgrade+"%");
            }
            //执行sql 得到一个结果集
            ResultSet rs = pstm.executeQuery();
            //处理结果集
            while(rs.next()){
                int id = rs.getInt("id");
                String sno1 = rs.getString("sno");
                String sname1 = rs.getString("sname");
                String sgrade1 = rs.getString("sgrade");
                stus.add(new Student(sno1,sname1,sgrade1));
            }
            //资源关闭
            rs.close();
            pstm.close();
            return stus;
        }catch (Exception e){
            System.out.println(e.getMessage());
            throw new RuntimeException();
        }
    }
    //插入信息
    public static int add(Student s){
        //链接数据库
        String url = "jdbc:mysql://localhost:3306/studentmanagement?" +
                "useSSL=false&serverTimezone=GMT%2B8" +
                "&allowPublicKeyRetrieval=true" +
                "&useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (Exception e){
            System.out.println(e.getMessage());
            throw new RuntimeException();
        }
        try {
            Connection con = DriverManager.getConnection(url,
                    user, password);
            //准备sql语句
            String sql = "insert into student values(NULL,?,?,?)";
            //执行对象
            PreparedStatement pstm = con.prepareStatement(sql);
            //绑定占位符
            pstm.setString(1,s.getSno());
            pstm.setString(2,s.getName());
            pstm.setString(3,s.getGrade());
            //执行语句
            int i = pstm.executeUpdate();
            //释放资源
            pstm.close();
            con.close();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //删除学生信息
    public static int delete(String sno){
        //链接数据库
        String url = "jdbc:mysql://localhost:3306/studentmanagement?" +
                "useSSL=false&serverTimezone=GMT%2B8" +
                "&allowPublicKeyRetrieval=true" +
                "&useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "123456";
        //加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        try {
            Connection con = DriverManager.getConnection(url,
                    user, password);
            //准备sql语句
            String sql = "delete from student where sno=?";
            //准备执行sql的对象
            PreparedStatement pstm = con.prepareStatement(sql);
            //绑定占位
            pstm.setString(1,sno);
            //执行sql
            int i = pstm.executeUpdate();
            //释放资源
            pstm.close();
            con.close();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
